package com.ymt.fmd.platform.service.ext;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.Clob;
import java.sql.SQLException;
import java.util.Date;
import java.util.Map;
import java.util.Set;
import java.util.logging.Logger;

import com.alibaba.fastjson.JSONObject;
import com.ymt.fmd.platform.service.BoDbResolver;
import com.ymt.fmd.util.DateUtil;

@SuppressWarnings("unchecked")
public class OracleBoDbResolver implements BoDbResolver {
	
	private Logger logger = Logger.getLogger(OracleBoDbResolver.class.getName());

	@Override
	public JSONObject transformResultMap(Map<String, ?> rs, int timezoneoffset) throws Exception {
		JSONObject map = new JSONObject();
		if (rs!=null) {
			for (String key : rs.keySet()) {
				Object v = rs.get(key);
				logger.finer(key+"============"+v.getClass().getName());
				if (v instanceof java.sql.Timestamp) {
					v = DateUtil.formatDate(
							new Date(((java.sql.Timestamp) v).getTime()),
							DateUtil.DATEFORMATSS_ZH, timezoneoffset);
				} else if (v instanceof Clob) {
					v = clobToString((Clob)v);
				} else if (v instanceof BigDecimal) {
					v = ((BigDecimal)v).toString();
				}
				map.put(key, v);
			}
		}
		return map;
	}
	
	public String getInsertSql(String tsId, String boName, JSONObject attrs, int timezoneoffset) {
		StringBuilder sbc = new StringBuilder("insert into ");
		sbc.append(boName).append(" (TS_ID");
		StringBuilder sbv = new StringBuilder("values (");
		sbv.append("'"+tsId+"'");
		for (String attr : (Set<String>)attrs.keySet()) {
			if (attr.equalsIgnoreCase("TS_ID")) {
				continue;
			}
			JSONObject ao = attrs.getJSONObject(attr);
			String v = null;
			Object val = ao.get("value");
			String def = ao.getString("default");
			if ("string".equalsIgnoreCase(ao.getString("type")) || "text".equalsIgnoreCase(ao.getString("type"))) {
				if (val==null || val.toString().length()==0) {
					val = def;
				}
				v = "'"+val+"'";
			} else if ("int".equalsIgnoreCase(ao.getString("type")) || "number".equalsIgnoreCase(ao.getString("type"))) {
				if (val==null || val.toString().length()==0) {
					if (def==null ||def.length()==0) continue;
					val = def;
				}
				v = val.toString();
			} else if ("datetime".equalsIgnoreCase(ao.getString("type"))) {
				if(val==null){
					v = null;
				}else{
					String sval = val.toString();
//					v = "to_date('"+(val.toString().length()==10? val.toString()+" 00:00:00" : val.toString())+"', 'yyyy-mm-dd hh24:mi:ss')";
					
					String pattern = "yyyy-MM-dd HH:mm:ss";
					if(sval.length()==10 && sval.indexOf("-")>0){
						pattern = "yyyy-MM-dd";
					}else if(sval.length()==8 && sval.indexOf(":")>0){
						pattern = "HH:mm:ss";
					}else if(sval.length()==16){
						pattern = "yyyy-MM-dd HH:mm";
					}else if(sval.length()==19){
						pattern = "yyyy-MM-dd HH:mm:ss";
					}
					
					//写入库的时候 - timezoneoffset； 读取的时候 + timezoneoffset；
					String nval = DateUtil.formatDate(DateUtil.formatStrToDate(sval, pattern), pattern, -timezoneoffset);
					
					if(sval.length()==10 && sval.indexOf("-")>0){
						v = "to_date('"+sval+"', 'yyyy-mm-dd')";
					}else if(sval.length()==8 && sval.indexOf(":")>0){
						v = "to_date('"+nval+"', 'hh24:mi:ss')";
					}else if(sval.length()==16){
						v = "to_date('"+nval+"', 'yyyy-mm-dd hh24:mi')";
					}else if(sval.length()==19){
						v = "to_date('"+nval+"', 'yyyy-mm-dd hh24:mi:ss')";
					}
				}
			}
			//append
			sbc.append(", ");
			sbv.append(", ");
			sbc.append(attr.toUpperCase());
			sbv.append(v);
		}
		String rtn = sbc.append(") ").append(sbv).append(")").toString();
		logger.finer("getInsertSql rtn="+rtn);
		return rtn;
	}
	
	public String getUpdateSql(String tsId, String boName, JSONObject attrs, int timezoneoffset) {
		StringBuilder sb = new StringBuilder("update ");
		sb.append(boName).append(" set ");
		int i=0;
		for (String attr : (Set<String>)attrs.keySet()) {
			if (attr.equalsIgnoreCase("TS_ID")) {
				continue;
			}
			JSONObject ao = attrs.getJSONObject(attr);
			String v = null;
			Object val = ao.get("value");
			String def = ao.getString("default");
			if ("string".equalsIgnoreCase(ao.getString("type")) || "text".equalsIgnoreCase(ao.getString("type"))) {
				if (val==null || val.toString().length()==0) {
					val = def;
				}
				v = "'"+val+"'";
			} else if ("integer".equalsIgnoreCase(ao.getString("type")) || "number".equalsIgnoreCase(ao.getString("type"))) {
				if (val==null || val.toString().length()==0) {
					if (def==null ||def.length()==0) continue;
					val = def;
				}
				v = val.toString();
			} else if ("datetime".equalsIgnoreCase(ao.getString("type"))) {
				if(val==null){
					v = null;
				}else{
//					v = "to_date('"+val.toString()+"', 'yyyy-mm-dd hh24:mi:ss')";
					String sval = val.toString();
					String pattern = "yyyy-MM-dd HH:mm:ss";
					if(sval.length()==10 && sval.indexOf("-")>0){
						pattern = "yyyy-MM-dd";
					}else if(sval.length()==8 && sval.indexOf(":")>0){
						pattern = "HH:mm:ss";
					}else if(sval.length()==16){
						pattern = "yyyy-MM-dd HH:mm";
					}else if(sval.length()==19){
						pattern = "yyyy-MM-dd HH:mm:ss";
					}
					//写入库的时候 - timezoneoffset； 读取的时候 + timezoneoffset；
					String nval = DateUtil.formatDate(DateUtil.formatStrToDate(sval, pattern), pattern, -timezoneoffset);
					
					if(sval.length()==10 && sval.indexOf("-")>0){
						v = "to_date('"+sval+"', 'yyyy-mm-dd')";
					}else if(sval.length()==8 && sval.indexOf(":")>0){
						v = "to_date('"+nval+"', 'hh24:mi:ss')";
					}else if(sval.length()==16){
						v = "to_date('"+nval+"', 'yyyy-mm-dd hh24:mi')";
					}else if(sval.length()==19){
						v = "to_date('"+nval+"', 'yyyy-mm-dd hh24:mi:ss')";
					}
				}
			}
			//append
			if (i>0) {
				sb.append(", ");
			}
			sb.append(attr.toUpperCase()).append("=")
				.append(v);
			i++;
		}
		sb.append(" where TS_ID='").append(tsId).append("'");
		logger.finer("getUpdateSql rtn="+sb);
		return sb.toString();
	}

	public String clobToString(Clob clob) throws SQLException, IOException {
		String reString = null;
		Reader is = clob.getCharacterStream();// 得到流
		BufferedReader br = new BufferedReader(is);
		String s = br.readLine();
		StringBuffer sb = new StringBuffer();
		while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
			sb.append(s);
			s = br.readLine();
		}
		reString = sb.toString();
		return reString;
	}
}
